package com.zzty.taskapp.daoImpl;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.bouncycastle.util.Strings;

import com.zzty.taskapp.common.DBUtil;
import com.zzty.taskapp.dao.TeamExamTransationDao;
import com.zzty.taskapp.entity.Teamexamtransation;

public class TeamExamTransationDaoImpl implements TeamExamTransationDao {

	public String searchState(int examId, String userIds) {
		String sql = "select state from teamexamtransation where Exameid=? and Userids=?";
		PreparedStatement pst = DBUtil.getPreparedStatement(sql);
		try {
			pst.setInt(1, examId);
			pst.setString(2, userIds);
			ResultSet rs = pst.executeQuery();
			String state = null;
			while (rs.next()) {
				state = rs.getString("state");
			}
			DBUtil.closeConnection();
			return state;
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;
	}

	public void updateState(int examId, String userIds, String userId) {

		String sql = "update teamexamtransation set state=? where Exameid=? and Userids=?";
		PreparedStatement pst = DBUtil.getPreparedStatement(sql);
		try {
			pst.setString(1, userId);
			pst.setInt(2, examId);
			pst.setString(3, userIds);
			pst.executeUpdate();
			DBUtil.closeConnection();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

	}

	public void updateStartTime(int examId, String userIds, String startTime) {
		String sql = "update teamexamtransation set Starttime=? where Exameid=? and Userids=?";
		PreparedStatement pst = DBUtil.getPreparedStatement(sql);
		try {
			pst.setString(1, startTime);
			pst.setInt(2, examId);
			pst.setString(3, userIds);
			pst.executeUpdate();
			DBUtil.closeConnection();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

	}

	public String searchStartTime(int examId, String userIds) {
		String sql = "select Starttime from teamexamtransation  where Exameid=? and Userids=?";
		PreparedStatement pst = DBUtil.getPreparedStatement(sql);
		try {
			pst.setInt(1, examId);
			pst.setString(2, userIds);
			ResultSet rs = pst.executeQuery();
			String startTime = null;
			while (rs.next()) {
				startTime = rs.getString("Starttime");
			}
			DBUtil.closeConnection();
			return startTime;
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;
	}

	public void updateTotaiTime(int examId, String userIds, int totalTime) {
		String sql = "update teamexamtransation set Totaltime=? where Exameid=? and Userids=?";
		PreparedStatement pst = DBUtil.getPreparedStatement(sql);
		try {
			pst.setInt(1, totalTime);
			pst.setInt(2, examId);
			pst.setString(3, userIds);
			pst.executeUpdate();
			DBUtil.closeConnection();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

	}

	public void updateAnswer(int examId, String userIds, String answer) {
		String sql = "update teamexamtransation set Answers=? where Exameid=? and Userids=?";
		PreparedStatement pst = DBUtil.getPreparedStatement(sql);
		try {
			pst.setString(1, answer);
			pst.setInt(2, examId);
			pst.setString(3, userIds);
			pst.executeUpdate();
			DBUtil.closeConnection();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

	}

	public List<String> searchUserids(int examId) {
		List<String> useridss = new ArrayList<String>();
		String sql = "select Userids from teamexamtransation where Exameid=?";
		PreparedStatement pst = DBUtil.getPreparedStatement(sql);
		try {
			pst.setInt(1, examId);
			ResultSet rs = pst.executeQuery();
			while (rs.next()) {
				String userids = rs.getString("Userids");

				useridss.add(userids);
			}

			return useridss;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return useridss;
	}

	public void updateIsValid(int examId, String userIds, int valid) {
		String sql = "update teamexamtransation set isvalid=? where Exameid=? and Userids=?";
		PreparedStatement pst = DBUtil.getPreparedStatement(sql);
		try {
			pst.setInt(1, valid);
			pst.setInt(2, examId);
			pst.setString(3, userIds);
			pst.executeUpdate();
			DBUtil.closeConnection();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

	}

	public int searchTotalTime(int examId, String userIds) {
		String sql = "select Totaltime from teamexamtransation where Exameid=? and Userids=?";
		PreparedStatement pst = DBUtil.getPreparedStatement(sql);
		try {
			pst.setInt(1, examId);
			pst.setString(2, userIds);
			ResultSet rs = pst.executeQuery();
			int totalTime = 0;
			while (rs.next()) {
				totalTime = rs.getInt("Totaltime");

			}
			return totalTime;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return 0;
	}

	public String searchAnswer(int examId, String userIds) {
		String sql = "select Answers from teamexamtransation where Exameid=? and Userids=?";
		PreparedStatement pst = DBUtil.getPreparedStatement(sql);
		String answerURL = "";
		try {
			pst.setInt(1, examId);
			pst.setString(2, userIds);
			ResultSet rs = pst.executeQuery();
			while (rs.next()) {
				answerURL = rs.getString("Answers");

			}
			DBUtil.closeConnection();
			return answerURL;
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return answerURL;
	}

	public void updateScore(int examId, String userIds, String score) {
		String sql = "update teamexamtransation set Score=? where Exameid=? and Userids=?";
		PreparedStatement pst = DBUtil.getPreparedStatement(sql);
		try {
			pst.setString(1, score);
			pst.setInt(2, examId);
			pst.setString(3, userIds);
			pst.executeUpdate();
			DBUtil.closeConnection();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

	}

	public void updateComment(int examId, String userIds, String comment) {
		String sql = "update teamexamtransation set comment=? where Exameid=? and Userids=?";
		PreparedStatement pst = DBUtil.getPreparedStatement(sql);
		try {
			pst.setString(1, comment);
			pst.setInt(2, examId);
			pst.setString(3, userIds);
			pst.executeUpdate();
			DBUtil.closeConnection();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

	}

	public int searchIsValid(int examId, String userIds) {
		String sql = "select isvalid from teamexamtransation where Exameid=? and Userids=?";
		PreparedStatement pst = DBUtil.getPreparedStatement(sql);
		int valid = 0;
		try {
			pst.setInt(1, examId);
			pst.setString(2, userIds);
			ResultSet rs = pst.executeQuery();
			while (rs.next()) {
				valid = rs.getInt("isvalid");

			}
			return valid;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return valid;
	}

	public String searchScore(int examId, String userIds) {
		String sql = "select Score from teamexamtransation where Exameid=? and Userids=?";
		PreparedStatement pst = DBUtil.getPreparedStatement(sql);
		String score = "";
		try {
			pst.setInt(1, examId);
			pst.setString(2, userIds);
			ResultSet rs = pst.executeQuery();
			while (rs.next()) {
				score = rs.getString("Score");

			}
			DBUtil.closeConnection();
			return score;
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return score;
	}

	public int searchValid(int examId, String userIds) {
		String sql = "select isValid from teamexamtransation where Exameid=? and Userids=?";
		PreparedStatement pst = DBUtil.getPreparedStatement(sql);
		int valid = 0;
		try {
			pst.setInt(1, examId);
			pst.setString(2, userIds);
			ResultSet rs = pst.executeQuery();
			while (rs.next()) {
				valid = rs.getInt("isValid");

			}
			DBUtil.closeConnection();
			return valid;
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return valid;
	}

	public void addTeamExamTransation(Teamexamtransation tet) {
		String sql = "insert into teamexamtransation (exameid,userids) values(?,?)";
		PreparedStatement pst = DBUtil.getPreparedStatement(sql);
		try {
			pst.setInt(1, tet.getExameid());
			pst.setString(2, tet.getUserids());
			pst.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

	}

	public void updateRank(int examId) {
		String sql = "update teamexamtransation set Rank=1 where Exameid=? and Score='A'";
		PreparedStatement pst = DBUtil.getPreparedStatement(sql);
		try {
			pst.setInt(1, examId);
			pst.executeUpdate();
			DBUtil.closeConnection();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public String searchWinnerIds(int examId) {
		String winnerIds = "";
		String sql = "select Userids from teamexamtransation where Exameid=? and Score='A'";
		PreparedStatement pst = DBUtil.getPreparedStatement(sql);
		try {
			pst.setInt(1, examId);
			ResultSet rs = pst.executeQuery();
			while (rs.next()) {
				winnerIds = rs.getString("Userids");
			}
			return winnerIds;
		} catch (Exception e) {
			e.printStackTrace();
		}
		return winnerIds;
	}

	public Map<String, String> searchScoreByExamid(int examid) {
		Map<String, String> map = new HashMap<String, String>();
		String sql = "select Userids,Score from teamexamtransation where Exameid=? ";
		PreparedStatement pst = DBUtil.getPreparedStatement(sql);
		try {
			pst.setInt(1, examid);
			ResultSet rs = pst.executeQuery();
			while (rs.next()) {
				map.put(rs.getString("Userids"), rs.getString("Score"));

			}
			return map;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return map;
	}

}
